﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using Lime49.Exceptions;
using LockCrypt.Core;
using System.IO;
using System.Collections;
using MySql.Data.MySqlClient;
using System.Text.RegularExpressions;

namespace LockCrypt.DAL {
    //public class MySQLProvider : NetworkDatabase {
    public class MySQLProvider : NetworkDatabase {
        private readonly IEncryptionService _encryptionService;
        private bool? _isConnected;
        private Version _dbVersion;

        /// <summary>
        /// Gets the encryption provider.
        /// </summary>
        /// <value>The encryption provider.</value>
        public override IEncryptionService Encryption {
            get { return _encryptionService; }
        }

        /// <summary>
        /// Gets the type of database which this provider can access.
        /// </summary>
        /// <value>The type of database which this provider can access.</value>
        public override Constants.DatabaseType DatabaseType {
            get { return Constants.DatabaseType.MySQL; }
        }

        /// <summary>
        /// Gets a value indicating whether this instance is either connected or can connect.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if this instance is connected or can connect, otherwise <c>false</c>.
        /// </value>
        public override bool IsConnected {
            get {
                if(!_isConnected.HasValue) {
                    _isConnected = CheckPassword(true);
                }
                return _isConnected.Value;
            }
        }

        /// <summary>
        /// Gets whether an attempt can be made to open a database connection.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if an attempt can be made to open a database connection, otherwise <c>false</c>.
        /// </value>
        public override bool CanAttemptConnection {
            get { return Credentials != null && (!string.IsNullOrEmpty(Credentials.Hostname) && Credentials.Port > 0 && Credentials.Port < 65536 && !string.IsNullOrEmpty(Credentials.Username) && !string.IsNullOrEmpty(Credentials.DatabaseName)); }
        }

        /// <summary>
        /// Gets a formatted description of this database connection.
        /// </summary>
        /// <value>A formatted description of this database connection.</value>
        public override string FormattedName {
            get { return string.Format("mysql://{0}:{1}/{2}", Credentials.Hostname, Credentials.Port, Credentials.DatabaseName); }
        }

        /// <summary>
        /// The connection string for SQLite. 
        /// Placeholder1 = hostname.
        /// Placeholder2 = port.
        /// Placeholder3 = database name.
        /// Placeholder4 = user.
        /// Placeholder5 = password.
        /// </summary>
        public const string MySQLConnectionString = "Server={0};Port={1};Database={2};User={3};Password={4};charset=utf8";

        /// <summary>
        /// Gets the database connection string.
        /// </summary>
        /// <value>The connection string.</value>
        public override string ConnectionString {
            get { return string.Format(MySQLConnectionString, Credentials.Hostname, Credentials.Port, Credentials.DatabaseName, Credentials.Username, Credentials.Password); }
        }

        public MySQLProvider()
            :base() {
            _encryptionService = new NullEncryptionService();
        }

        /// <summary>
        /// Initializes a new <see cref="MySQLProvider"/>.
        /// </summary>
        /// <param name="credentials">The credentials used to connect.</param>
        /// <param name="encryptionService">The encryption service.</param>
        public MySQLProvider(NetworkDbCredentials credentials, IEncryptionService encryptionService)
            : base(credentials) {
            _encryptionService = encryptionService;
        }

        /// <summary>
        /// Initializes a new <see cref="MySQLProvider"/>.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="encryptionService">The encryption service.</param>
        public MySQLProvider(string connectionString, IEncryptionService encryptionService)
            : base() {
            try {
                Match match = Regex.Match(connectionString, @"Server=([\w\.]+?);\s*?(?:Port=(\d+?);\s*?)?Database=(\w+?);\s*?(?:(?:User)|(?:Uid))=(\w+?);\s*?(?:(?:Password)|(?:Pwd))=(\w*?);.*", RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.CultureInvariant);
                if(!match.Success)
                    throw new LockCryptException("Invalid connection string", 234);
                string host = match.Groups[1].Value,
                       dbName = match.Groups[3].Value,
                       userName = match.Groups[4].Value,
                       pass = match.Groups[5].Value;
                int port = int.Parse(match.Groups[2].Value);
                this.Credentials = new NetworkDbCredentials(host, port, userName, pass, dbName, string.Empty);
            } catch {
                throw new LockCryptException("Invalid connection string", 234);
            }
            _encryptionService = encryptionService;
        }

        #region Accounts
        /// <summary>
        /// Adds accounts to the default database.
        /// </summary>
        /// <param name="accounts">The accounts to add.</param>
        /// <param name="logger">The delegate to use to log progress (can be null).</param>
        /// <returns><c>null</c> on success, a log of errors on failure.</returns>
        public override void AddAccounts(IEnumerable<IAccount> accounts, ILogProvider logger) {
            MySqlConnection conn = null;
            if(logger == null) {
                // if there's no log delegate, log to console.
                logger = _logger;
            }
            MultipleExceptionLogger logMultiple = new MultipleExceptionLogger();
            CompositeLogger logComposite = new CompositeLogger(logger, logMultiple);
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {
                    AddAccounts(conn, trans, accounts, logComposite);
                    trans.Commit();
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
            if(logMultiple.MessageLog.Count > 0) {
                logMultiple.MessageLog.Insert(0, new Exception(I18nUtils.GetString("Errors", "NonFatalErrors")));
                throw new MultipleException(logMultiple.MessageLog);
            }
        }

        /// <summary>
        /// Add accounts using an existing transation.
        /// </summary>
        /// <param name="conn">The connection.</param>
        /// <param name="transaction">The transaction.</param>
        /// <param name="accounts">The accounts.</param>
        /// <param name="logger">The logger.</param>
        private void AddAccounts(MySqlConnection conn, MySqlTransaction transaction, IEnumerable<IAccount> accounts, ILogProvider logger) {
            using(MySqlCommand cmdAccount = conn.CreateCommand())
            using(MySqlCommand cmdAddFields = conn.CreateCommand()) {
                cmdAccount.Transaction = transaction;
                cmdAddFields.Transaction = transaction;
                var dummyArray = new byte[0];
                cmdAccount.CommandText = "INSERT INTO accounts (accountid, name, parentid, templateid, created, modified, icon, notes, autotype) VALUES (@accountid, @name, @parentid, @templateid, @created, @modified, @icon, @notes, @autotype)";
                cmdAccount.Parameters.AddWithValue("@accountid", string.Empty);
                cmdAccount.Parameters.AddWithValue("@name", dummyArray);
                cmdAccount.Parameters.AddWithValue("@parentid", string.Empty);
                cmdAccount.Parameters.AddWithValue("@templateid", string.Empty);
                cmdAccount.Parameters.AddWithValue("@created", DateTime.Now);
                cmdAccount.Parameters.AddWithValue("@modified", DateTime.Now);
                cmdAccount.Parameters.AddWithValue("@icon", dummyArray);
                cmdAccount.Parameters.AddWithValue("@notes", dummyArray);
                cmdAccount.Parameters.AddWithValue("@autotype", dummyArray);
                cmdAddFields.CommandText = "INSERT INTO accountfields (accountid, fieldname, fieldvalue, meta, sortindex) VALUES (@accountid, @fieldname, @fieldvalue, @meta, @sortindex)";
                cmdAddFields.Parameters.AddWithValue("@accountid", string.Empty);
                cmdAddFields.Parameters.AddWithValue("@fieldname", dummyArray);
                cmdAddFields.Parameters.AddWithValue("@fieldvalue", dummyArray);
                cmdAddFields.Parameters.AddWithValue("@meta", dummyArray);
                cmdAddFields.Parameters.AddWithValue("@sortindex", 0);
                foreach(IAccount account in accounts) {
                    try {
                        cmdAccount.Parameters["@accountid"].Value = account.ID;
                        cmdAccount.Parameters["@name"].Value = _encryptionService.EncryptString(account.Name);
                        cmdAccount.Parameters["@parentid"].Value = account.ParentID;
                        cmdAccount.Parameters["@templateid"].Value = account.Template == null ? null : account.Template.ID;
                        cmdAccount.Parameters["@created"].Value = account.Created;
                        cmdAccount.Parameters["@modified"].Value = account.Modified;
                        cmdAccount.Parameters["@icon"].Value = _encryptionService.EncryptString(account.Icon);
                        cmdAccount.Parameters["@notes"].Value = _encryptionService.EncryptString(account.Notes);
                        cmdAccount.Parameters["@autotype"].Value = _encryptionService.EncryptString(account.AutoTypeConfig.ToString());
                        cmdAccount.ExecuteNonQuery();

                        cmdAddFields.Parameters["@accountid"].Value = account.ID;
                        foreach(IField field in account.Fields) {
                            try {
                                cmdAddFields.Parameters["@fieldname"].Value = _encryptionService.EncryptString(field.Name);
                                cmdAddFields.Parameters["@fieldvalue"].Value = _encryptionService.EncryptString(field.Value);
                                cmdAddFields.Parameters["@meta"].Value = _encryptionService.EncryptObject((int)field.Meta);
                                cmdAddFields.Parameters["@sortindex"].Value = field.SortIndex;
                                cmdAddFields.ExecuteNonQuery();
                            } catch(Exception ex) {
                                logger.Log(string.Format("Error adding field for account '{0}': {1}", account.Name, ex.Message), true);
                            }
                        }
                    } catch(Exception ex) {
                        logger.Log(string.Format("Error adding account: {0}", ex.Message), true);
                    }
                }
            }
        }

        /// <summary>
        /// Edits an account.
        /// </summary>
        /// <param name="account">The account.</param>
        public override void EditAccount(IAccount account) {
            MySqlConnection conn = null;
            MultipleExceptionLogger logger = new MultipleExceptionLogger();
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {
                    using(MySqlCommand cmd = conn.CreateCommand()) {
                        cmd.Transaction = trans;
                        var dummyArray = new byte[0];
                        cmd.CommandText = "UPDATE accounts SET name = @name, parentid = @parentid, templateid = @templateid, icon = @icon, notes = @notes, autotype = @autotype WHERE accountid = @accountid";
                        cmd.Parameters.AddWithValue("@accountid", account.ID);
                        cmd.Parameters.AddWithValue("@name", _encryptionService.EncryptString(account.Name));
                        cmd.Parameters.AddWithValue("@parentid", account.ParentID);
                        cmd.Parameters.AddWithValue("@templateid", account.Template.ID);
                        cmd.Parameters.AddWithValue("@icon", _encryptionService.EncryptString(account.Icon));
                        cmd.Parameters.AddWithValue("@notes", _encryptionService.EncryptString(account.Notes));
                        cmd.Parameters.AddWithValue("@autotype", _encryptionService.EncryptString(account.AutoTypeConfig.ToString()));
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "DELETE FROM accountfields WHERE accountid = @accountid";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "INSERT INTO accountfields (accountid, fieldname, fieldvalue, meta, sortindex) VALUES (@accountid, @fieldname, @fieldvalue, @meta, @sortindex)";
                        cmd.Parameters.AddWithValue("@fieldname", dummyArray);
                        cmd.Parameters.AddWithValue("@fieldvalue", dummyArray);
                        cmd.Parameters.AddWithValue("@meta", dummyArray);
                        cmd.Parameters.AddWithValue("@sortindex", 0);
                        foreach(IField field in account.Fields) {
                            try {
                                cmd.Parameters["@fieldname"].Value = _encryptionService.EncryptString(field.Name);
                                cmd.Parameters["@fieldvalue"].Value = _encryptionService.EncryptString(field.Value);
                                cmd.Parameters["@meta"].Value = _encryptionService.EncryptObject((int)field.Meta);
                                cmd.Parameters["@sortindex"].Value = field.SortIndex;
                                cmd.ExecuteNonQuery();
                            } catch(Exception ex) {
                                logger.Log(string.Format("Error adding field for account '{0}': {1}", account.Name, ex.Message), true);
                            }
                        }
                    }
                    trans.Commit();
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch {
                    }
                }
            }
            if(logger.MessageLog.Count > 0) {
                logger.MessageLog.Insert(0, new Exception("Account was saved with errors"));
                throw new MultipleException(logger.MessageLog);
            }
        }

        /// <summary>
        /// Deletes an account.
        /// </summary>
        /// <param name="accountId">The account ID.</param>
        public override void DeleteAccount(string accountId) {
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                    using(MySqlCommand cmd = conn.CreateCommand()) {
                        cmd.CommandText = "DELETE FROM accounts WHERE accountid = @accountid";
                        cmd.Parameters.AddWithValue("@accountid", accountId);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "DELETE FROM accountfields WHERE accountid = @accountid";
                        cmd.ExecuteNonQuery();
                    }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Deletes multiple accounts.
        /// </summary>
        /// <param name="accountIds">The account IDs.</param>
        public override void DeleteAccounts(IEnumerable<string> accountIds) {
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlCommand cmd = conn.CreateCommand()) {
                    cmd.CommandText = "DELETE FROM accounts WHERE accountid = @accountid";
                    cmd.Parameters.AddWithValue("@accountid", string.Empty);
                    foreach(var id in accountIds) {
                        cmd.Parameters["@accountid"].Value = id;
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "DELETE FROM accountfields WHERE accountid = @accountid";
                        cmd.ExecuteNonQuery();
                    }
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Gets a list of recipes from the current database file.
        /// </summary>
        /// <returns>A list of recipes in the database.</returns>
        public override List<IAccount> GetAccounts() {
            MySqlConnection conn = null;
            List<IAccount> accounts = new List<IAccount>();
            try {
                conn = GetConnection();
                conn.Open();
                accounts = GetAccounts(conn);
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }

            return accounts;
        }

        /// <summary>
        /// Gets acccounts using an open connection.
        /// </summary>
        /// <param name="conn">The conn connection.</param>
        /// <returns>All accounts in the database.</returns>
        private List<IAccount> GetAccounts(MySqlConnection conn) {
            List<IAccount> accounts = new List<IAccount>();
            using(MySqlCommand cmdAccounts = conn.CreateCommand())
            using(MySqlCommand cmdAccountFields = conn.CreateCommand())
            using(MySqlCommand cmdTemplates = conn.CreateCommand()) {
                cmdAccounts.CommandText = "SELECT accountid, name, parentid, templateid, created, modified, icon, notes, autotype FROM accounts";
                cmdAccountFields.CommandText = "SELECT fieldname, fieldvalue, meta, sortindex FROM accountfields WHERE accountid = @accountid";
                cmdAccountFields.Parameters.AddWithValue("@accountid", string.Empty);
                cmdTemplates.CommandText = "SELECT name, shownotes FROM templates WHERE templateid = @templateid";
                cmdTemplates.Parameters.AddWithValue("@templateid", string.Empty);
                Dictionary<string, ITemplate> templates = new Dictionary<string, ITemplate>();
                MySqlDataReader reader = cmdAccounts.ExecuteReader();
                while(reader.Read()) {
                    try {
                        string parentId = Convert.ToString(reader[2]);
                        AccountBase account = new AccountBase() {
                            ID = Convert.ToString(reader[0]),
                            Name = _encryptionService.DecryptString(GetBytes(1, reader)),
                            ParentID = string.IsNullOrEmpty(parentId) ? null : parentId,
                            Created = Convert.ToDateTime(reader["created"]),
                            Modified = Convert.ToDateTime(reader["modified"]),
                            Icon = _encryptionService.DecryptString(GetBytes(6, reader)),
                            Notes = _encryptionService.DecryptString(GetBytes(7, reader)),
                            AutoTypeConfig = new LockCrypt.Core.AutoType.AutoTypeConfiguration(_encryptionService.DecryptString(GetBytes(8, reader))),
                            Template = new TemplateBase() {
                                ID = Convert.ToString(reader[3]),
                            }
                        };
                        accounts.Add(account);
                    } catch(Exception ex) {
                        _logger.Log(string.Format("Error loading account: {0}", ex.Message), true);
                    }
                }
                reader.Close();
                foreach(IAccount account in accounts) {
                    try {
                        cmdAccountFields.Parameters["@accountid"].Value = account.ID;
                        reader = cmdAccountFields.ExecuteReader();
                        while(reader.Read()) {
                            string fieldMeta = _encryptionService.DecryptString(GetBytes(2, reader));
                            FieldBase field = new FieldBase() {
                                Name = _encryptionService.DecryptString(GetBytes(0, reader)),
                                Value = _encryptionService.DecryptString(GetBytes(1, reader)),
                                Meta = string.IsNullOrEmpty(fieldMeta) ? FieldMetaData.None : (FieldMetaData)Enum.Parse(typeof(FieldMetaData), fieldMeta),
                                SortIndex = Convert.ToInt32(reader["sortindex"])
                            };
                            account.Fields.Add(field);
                        }
                        reader.Close();
                        if(string.IsNullOrEmpty(account.Template.ID)) {
                            account.Template = new TemplateBase() { Name = "(Deleted)", ShowNotes = true };
                        } else {
                            if(templates.ContainsKey(account.Template.ID)) {
                                account.Template = templates[account.Template.ID];
                            } else {
                                cmdTemplates.Parameters["@templateid"].Value = account.Template.ID;
                                reader = cmdTemplates.ExecuteReader();
                                if(reader.Read()) {
                                    TemplateBase template = new TemplateBase() {
                                        ID = account.Template.ID,
                                        Name = _encryptionService.DecryptString(GetBytes(0, reader)),
                                        ShowNotes = Convert.ToInt32(reader["shownotes"]) == 1
                                    };
                                    account.Template = template;
                                    templates.Add(template.ID, template);
                                }
                            }
                        }
                    } catch(Exception ex) {
                        _logger.Log(string.Format("Error loading account: {0}", ex.Message), true);
                    } finally {
                        reader.Close();
                    }
                }
            }
            return accounts;
        }
        #endregion

        #region Groups
        /// <summary>
        /// Adds account groups to the default database.
        /// </summary>
        /// <param name="groups">The groups to add.</param>
        /// <param name="logger">The delegate to use to log progress (can be null).</param>
        public override void AddGroups(IEnumerable<IGroup> groups, ILogProvider logger) {
            MySqlConnection conn = null;
            if(logger == null) {
                // if there's no logger, use the default.
                logger = _logger;
            }
            MultipleExceptionLogger logMultiple = new MultipleExceptionLogger();
            CompositeLogger logComposite = new CompositeLogger(logger, logMultiple);
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {
                    AddGroups(conn, trans, groups, logComposite);
                    trans.Commit();
                }
            } catch {
                throw;
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
            if(logMultiple.MessageLog.Count > 0) {
                logMultiple.MessageLog.Insert(0, new Exception(I18nUtils.GetString("Errors", "NonFatalErrors")));
                throw new MultipleException(logMultiple.MessageLog);
            }
        }

        /// <summary>
        /// Adds groups using an existing transaction.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="transaction">The transaction.</param>
        /// <param name="groups">The groups.</param>
        /// <param name="logger">The logger.</param>
        private void AddGroups(MySqlConnection connection, MySqlTransaction transaction, IEnumerable<IGroup> groups, ILogProvider logger) {
            using(MySqlCommand cmd = connection.CreateCommand()) {
                cmd.Transaction = transaction;
                cmd.CommandText = "INSERT INTO groups (groupid, name, parentid, sortindex) VALUES (@groupid, @name, @parentid, @sortindex)";
                var dummyArray = new byte[0];
                cmd.Parameters.AddWithValue("@groupid", string.Empty);
                cmd.Parameters.AddWithValue("@name", dummyArray);
                cmd.Parameters.AddWithValue("@parentid", string.Empty);
                cmd.Parameters.AddWithValue("@sortindex", 0);
                foreach(IGroup group in groups) {
                    try {
                        cmd.Parameters["@groupid"].Value = group.ID;
                        cmd.Parameters["@name"].Value = _encryptionService.EncryptString(group.Name);
                        cmd.Parameters["@parentid"].Value = group.ParentID;
                        cmd.Parameters["@sortindex"].Value = group.SortIndex;
                        cmd.ExecuteNonQuery();
                    } catch(Exception ex) {
                        logger.Log(string.Format("Error adding group: {0}", ex.Message), true);
                    }
                }
            }
        }

        /// <summary>
        /// Edits a group.
        /// </summary>
        /// <param name="group">The group.</param>
        public override void EditGroup(IGroup group) {
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {
                    using(MySqlCommand cmd = conn.CreateCommand()) {
                        cmd.CommandText = "UPDATE groups SET name = @name, parentid = @parentid, sortindex = @sortindex WHERE groupid = @groupid";
                        cmd.Parameters.AddWithValue("@groupid", group.ID);
                        cmd.Parameters.AddWithValue("@name", _encryptionService.EncryptString(group.Name));
                        cmd.Parameters.AddWithValue("@parentid", group.ParentID);
                        cmd.Parameters.AddWithValue("@sortindex", group.SortIndex);
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
            } catch {
                throw;
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch {
                    }
                }
            }
        }

        /// <summary>
        /// Deletes a group and all child groups/accounts.
        /// </summary>
        /// <param name="group">The group.</param>
        public override void DeleteGroup(IGroup group) {
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {
                    var groups = GetGroups();
                    IEnumerable<IGroup> nestedGroups = LockCryptBLL.ArrangeGroups(groups);
                    IGroup groupToDelete = null; // find the group in the heirarchy
                    foreach(var nestedGroup in nestedGroups) {
                        // search top level groups
                        groupToDelete = nestedGroup.FindGroup(group.ID);
                        if(groupToDelete != null)
                            break;
                    }
                    IEnumerable<IGroup> unavailableGroups = groupToDelete.Flatten();
                    IEnumerable<string> groupIds = unavailableGroups.Select(g => g.ID);

                    using(MySqlCommand cmdDeleteGroups = conn.CreateCommand())
                    using(MySqlCommand cmdDeleteAccounts = conn.CreateCommand()) {
                        cmdDeleteGroups.Transaction = trans;
                        cmdDeleteAccounts.Transaction = trans;
                        cmdDeleteGroups.CommandText = "DELETE FROM groups WHERE groupid = @groupid";
                        cmdDeleteGroups.Parameters.AddWithValue("@groupid", string.Empty);
                        cmdDeleteAccounts.CommandText = "DELETE FROM accounts WHERE parentid = @parentid";
                        cmdDeleteAccounts.Parameters.AddWithValue("@parentid", string.Empty);
                        foreach(string groupId in groupIds) {
                            cmdDeleteGroups.Parameters["@groupid"].Value = groupId;
                            cmdDeleteGroups.ExecuteNonQuery();
                            cmdDeleteAccounts.Parameters["@parentid"].Value = groupId;
                            cmdDeleteAccounts.ExecuteNonQuery();
                        }
                        //string inClause = string.Format("IN ('{0}')", groupIds.Aggregate((ids, id) => ids + "', '" + ));
                    }
                    trans.Commit();
                }
            } catch {
                throw;
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Deletes multiple groups and their child accounts. Does not delete child groups.
        /// </summary>
        /// <param name="groupIds">The group IDs.</param>
        public override void DeleteGroups(IEnumerable<string> groupIds) {
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {

                    using(MySqlCommand cmdDeleteGroups = conn.CreateCommand())
                    using(MySqlCommand cmdDeleteAccounts = conn.CreateCommand()) {
                        cmdDeleteGroups.Transaction = trans;
                        cmdDeleteAccounts.Transaction = trans;
                        cmdDeleteGroups.CommandText = "DELETE FROM groups WHERE groupid = @groupid";
                        cmdDeleteGroups.Parameters.AddWithValue("@groupid", string.Empty);
                        cmdDeleteAccounts.CommandText = "DELETE FROM accounts WHERE parentid = @parentid";
                        cmdDeleteAccounts.Parameters.AddWithValue("@parentid", string.Empty);
                        foreach(string groupId in groupIds) {
                            cmdDeleteGroups.Parameters["@groupid"].Value = groupId;
                            cmdDeleteGroups.ExecuteNonQuery();
                            cmdDeleteAccounts.Parameters["@parentid"].Value = groupId;
                            cmdDeleteAccounts.ExecuteNonQuery();
                        }
                        //string inClause = string.Format("IN ('{0}')", groupIds.Aggregate((ids, id) => ids + "', '" + ));
                    }
                    trans.Commit();
                }
            } catch {
                throw;
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Gets a list of account groups from the current database.
        /// </summary>
        /// <returns>A list of account groups in the database.</returns>
        public override List<IGroup> GetGroups() {
            MySqlConnection conn = null;
            List<IGroup> groups = new List<IGroup>();
            try {
                conn = GetConnection();
                conn.Open();
                groups = GetGroups(conn);
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }

            return groups;
        }

        /// <summary>
        /// Gets groups using an open connection.
        /// </summary>
        /// <param name="conn">The open connection.</param>
        /// <returns>All groups in the database.</returns>
        private List<IGroup> GetGroups(MySqlConnection conn) {
            List<IGroup> groups = new List<IGroup>();
            using(MySqlCommand cmd = conn.CreateCommand()) {
                cmd.CommandText = "SELECT groupid, name, parentid, sortindex FROM groups";
                MySqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    try {
                        string parentId = Convert.ToString(reader[2]);
                        GroupBase group = new GroupBase() {
                            ID = Convert.ToString(reader[0]),
                            Name = _encryptionService.DecryptString(GetBytes(1, reader)),
                            ParentID = string.IsNullOrEmpty(parentId) ? null : parentId,
                            SortIndex = Convert.ToInt32(reader["sortindex"])
                        };
                        groups.Add(group);
                    } catch(Exception ex) {
                        _logger.Log(string.Format("Error loading group: {0}", ex.Message), true);
                    }
                }
                reader.Close();
            }
            return groups;
        }

        #endregion
        
        #region Templates
        /// <summary>
        /// Adds templates to the default database.
        /// </summary>
        /// <param name="templates">The templates to add.</param>
        /// <param name="logger">The delegate to use to log progress (can be null).</param>
        public override void AddTemplates(IEnumerable<ITemplate> templates, ILogProvider logger) {
            MySqlConnection conn = null;
            if(logger == null) {
                // if there's no log delegate, log to console.
                logger = new ConsoleLogger();
            }
            MultipleExceptionLogger logMultiple = new MultipleExceptionLogger();
            CompositeLogger logComposite = new CompositeLogger(logger, logMultiple);
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {
                    AddTemplates(conn, trans, templates, logComposite);
                    trans.Commit();
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
            if(logMultiple.MessageLog.Count > 0) {
                logMultiple.MessageLog.Insert(0, new Exception(I18nUtils.GetString("Errors", "NonFatalErrors")));
                throw new MultipleException(logMultiple.MessageLog);
            }
        }

        /// <summary>
        /// Adds templates using an existing transaction.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="transaction">The transaction.</param>
        /// <param name="templates">The templates.</param>
        /// <param name="logger">The logger.</param>
        private void AddTemplates(MySqlConnection connection, MySqlTransaction transaction, IEnumerable<ITemplate> templates, ILogProvider logger) {
            using(MySqlCommand cmdTemplate = connection.CreateCommand())
            using(MySqlCommand cmdAddFields = connection.CreateCommand()) {
                cmdAddFields.Transaction = transaction;
                cmdTemplate.Transaction = transaction;
                var dummyArray = new byte[0];
                cmdTemplate.CommandText = "INSERT INTO templates (templateid, name, shownotes, canuseredit) VALUES (@templateid, @name, @shownotes, @canuseredit)";
                cmdTemplate.Parameters.AddWithValue("@templateid", string.Empty);
                cmdTemplate.Parameters.AddWithValue("@name", dummyArray);
                cmdTemplate.Parameters.AddWithValue("@shownotes", 0);
                cmdTemplate.Parameters.AddWithValue("@canuseredit", 0);
                cmdAddFields.CommandText = "INSERT INTO templatefields (templateid, fieldname, meta, sortindex) VALUES (@templateid, @fieldname, @meta, @sortindex)";
                cmdAddFields.Parameters.AddWithValue("@templateid", string.Empty);
                cmdAddFields.Parameters.AddWithValue("@fieldname", dummyArray);
                cmdAddFields.Parameters.AddWithValue("@meta", dummyArray);
                cmdAddFields.Parameters.AddWithValue("@sortindex", 0);
                foreach(ITemplate template in templates) {
                    try {
                        cmdTemplate.Parameters["@templateid"].Value = template.ID;
                        cmdTemplate.Parameters["@name"].Value = _encryptionService.EncryptString(template.Name);
                        cmdTemplate.Parameters["@shownotes"].Value = template.ShowNotes ? 1 : 0;
                        cmdTemplate.Parameters["@canuseredit"].Value = template.CanUserEdit ? 1 : 0;
                        cmdTemplate.ExecuteNonQuery();

                        cmdAddFields.Parameters["@templateid"].Value = template.ID;
                        foreach(IField field in template.Fields) {
                            try {
                                cmdAddFields.Parameters["@fieldname"].Value = _encryptionService.EncryptString(field.Name);
                                cmdAddFields.Parameters["@meta"].Value = _encryptionService.EncryptObject((int)field.Meta);
                                cmdAddFields.Parameters["@sortindex"].Value = field.SortIndex;
                                cmdAddFields.ExecuteNonQuery();
                            } catch(Exception ex) {
                                logger.Log(string.Format("Error adding field for template '{0}': {1}", template.Name, ex.Message), true);
                            }
                        }
                    } catch(Exception ex) {
                        logger.Log(string.Format("Error adding template: {0}", ex.Message), true);
                    }
                }
            }
        }

        /// <summary>
        /// Edits a template.
        /// </summary>
        /// <param name="template">The template.</param>
        public override void EditTemplate(ITemplate template) {
            MySqlConnection conn = null;
            MultipleExceptionLogger logger = new MultipleExceptionLogger();
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {
                    using(MySqlCommand cmd = conn.CreateCommand()) {
                        cmd.Transaction = trans;
                        cmd.CommandText = "UPDATE templates SET name = @name, shownotes = @shownotes WHERE templateid = @templateid";
                        cmd.Parameters.AddWithValue("@name", _encryptionService.EncryptString(template.Name));
                        cmd.Parameters.AddWithValue("@shownotes", template.ShowNotes ? 1 : 0);
                        cmd.Parameters.AddWithValue("@templateid", template.ID);
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "DELETE FROM templatefields WHERE templateid = @templateid";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "INSERT INTO templatefields (templateid, fieldname, meta, sortindex) VALUES (@templateid, @fieldname, @meta, @sortindex)";
                        var dummyArray = new byte[0];
                        cmd.Parameters.AddWithValue("@fieldname", dummyArray);
                        cmd.Parameters.AddWithValue("@meta", dummyArray);
                        cmd.Parameters.AddWithValue("@sortindex", 0);
                        foreach(IField field in template.Fields) {
                            try {
                                cmd.Parameters["@fieldname"].Value = _encryptionService.EncryptString(field.Name);
                                cmd.Parameters["@meta"].Value = _encryptionService.EncryptObject((int)field.Meta);
                                cmd.Parameters["@sortindex"].Value = field.SortIndex;
                                cmd.ExecuteNonQuery();
                            } catch(Exception ex) {
                                logger.Log(string.Format("Error adding field for template '{0}': {1}", template.Name, ex.Message), true);
                            }
                        }
                    }
                    trans.Commit();
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch {
                    }
                }
            }
            if(logger.MessageLog.Count > 0) {
                logger.MessageLog.Insert(0, new Exception("Template was saved with errors"));
                throw new MultipleException(logger.MessageLog);
            }
        }

        /// <summary>
        /// Deletes a template.
        /// </summary>
        /// <param name="templateId">The template ID.</param>
        public override void DeleteTemplate(string templateId) {
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlCommand cmd = conn.CreateCommand()) {
                    cmd.CommandText = "DELETE FROM templates WHERE templateid = @templateid";
                    cmd.Parameters.AddWithValue("@templateid", templateId);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DELETE FROM templatefields WHERE templateid = @templateid";
                    cmd.ExecuteNonQuery();
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Gets a list of templates from the current database file.
        /// </summary>
        /// <returns>A list of templates in the database.</returns>
        public override List<ITemplate> GetTemplates() {
            return GetTemplatesByName(null);
        }

        /// <summary>
        /// Gets matching templates by name.
        /// </summary>
        /// <param name="templateName">Name of the template.</param>
        /// <returns>All templates with a matching name.</returns>
        public override List<ITemplate> GetTemplatesByName(string templateName) {
            MySqlConnection conn = null;
            List<ITemplate> templates = null;
            try {
                conn = GetConnection();
                conn.Open();
                templates = GetTemplates(conn, templateName);
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
            return templates ?? new List<ITemplate>();
        }

        /// <summary>
        /// Gets templates using an open connection.
        /// </summary>
        /// <param name="conn">The open database connection.</param>
        /// <param name="templateName">Name of the template, or <c>null</c>.</param>
        /// <returns></returns>
        private List<ITemplate> GetTemplates(MySqlConnection conn, string templateName) {
            List<ITemplate> templates = new List<ITemplate>();
            using(MySqlCommand cmd = conn.CreateCommand()) {
                cmd.CommandText = "SELECT templateid, name, shownotes, canuseredit FROM templates";
                if(!string.IsNullOrEmpty(templateName)) {
                    cmd.CommandText += " WHERE name = @name";
                    cmd.Parameters.AddWithValue("@name", _encryptionService.EncryptString(templateName));
                }
                MySqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    try {
                        TemplateBase template = new TemplateBase()
                                                    {
                                                        ID = Convert.ToString(reader[0]),
                                                        Name = _encryptionService.DecryptString(GetBytes(1, reader)),
                                                        ShowNotes = Convert.ToInt32(reader["shownotes"]) == 1,
                                                        CanUserEdit = Convert.ToInt32(reader["canuseredit"]) == 1
                                                    };
                        templates.Add(template);
                    } catch(Exception ex) {
                        _logger.Log(string.Format("Error loading template: {0}", ex.Message), true);
                    }
                }
                reader.Close();

                cmd.CommandText = "SELECT fieldname, meta, sortindex FROM templatefields WHERE templateid = @templateid";
                cmd.Parameters.AddWithValue("@templateid", string.Empty);
                foreach(ITemplate template in templates) {
                    try {
                        cmd.Parameters["@templateid"].Value = template.ID;
                        reader = cmd.ExecuteReader();
                        while(reader.Read()) {
                            string fieldMeta = _encryptionService.DecryptString(GetBytes(1, reader));
                            FieldBase field = new FieldBase()
                                                  {
                                                      Name = _encryptionService.DecryptString(GetBytes(0, reader)),
                                                      Meta = string.IsNullOrEmpty(fieldMeta) ? FieldMetaData.None : (FieldMetaData) Enum.Parse(typeof(FieldMetaData), fieldMeta),
                                                      SortIndex = Convert.ToInt32(reader["sortindex"])
                                                  };
                            template.Fields.Add(field);
                        }
                        reader.Close();
                    } catch(Exception ex) {
                        _logger.Log(string.Format("Error loading fields for template '{0}': {1}", template.Name, ex.Message), true);
                    } finally {
                        reader.Close();
                    }
                }
            }
            return templates;
        }
        #endregion

        #region Maintenance
        /// <summary>
        /// Checks the password entered is valid.
        /// </summary>
        /// <returns>
        /// 	<c>true</c> if the current password is valid, otherwise <c>false</c>.
        /// </returns>
        public override bool CheckPassword() {
            return CheckPassword(false);
        }

        /// <summary>
        /// Checks the password entered is valid.
        /// </summary>
        /// <returns>
        /// 	<c>true</c> if the current password is valid, otherwise <c>false</c>.
        /// </returns>
        public bool CheckPassword(bool suppressExceptions) {
            bool valid = false;
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlCommand cmd = conn.CreateCommand()) {
                    cmd.CommandText = @"SELECT `value` FROM `meta` WHERE name = @name;";
                    cmd.Parameters.AddWithValue("@name", _encryptionService.EncryptString("version"));
                    MySqlDataReader reader = cmd.ExecuteReader();
                    if(reader.Read()) {
                        string versionString = _encryptionService.DecryptString(GetBytes(0, reader));
                        _dbVersion = new Version(versionString);
                        if(_dbVersion >= new Version("1.0.0.0")) {
                            valid = true;
                            _isConnected = true;
                        }
                    }
                    reader.Close();
                }
            } catch(Exception ex) {
                if(!suppressExceptions) {
                    if(ex.Message.ToLowerInvariant().Contains("not exist")) {
                        throw new LockCryptException("Database tables do not exist", 236);
                    } else {
                        throw;
                    }
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch {
                    }
                }
            }
            return valid;
        }

        /// <summary>
        /// Creates the required tables.
        /// </summary>
        public override void CreateDatabase() {
            CreateTables();
        }

        /// <summary>
        /// Clears the database.
        /// </summary>
        public override void ClearDatabase() {
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlCommand cmd = conn.CreateCommand()) {
                    cmd.CommandText = @"DELETE FROM accounts;
                        DELETE FROM accountfields;
                        DELETE FROM templates;
                        DELETE FROM templatefields;
                        DELETE FROM groups;
                    ";
                    cmd.ExecuteNonQuery();
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch {
                    }
                }
            }
        }

        /// <summary>
        /// Removes the database tables.
        /// </summary>
        public override void RemoveDatabase() {
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlCommand cmd = conn.CreateCommand()) {
                    cmd.CommandText = @"DROP TABLE accounts, accountfields, templates, templatefields, groups, meta;";
                    cmd.ExecuteNonQuery();
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch {
                    }
                }
            }
        }

        /// <summary>
        /// Reads the whole contents of the database.
        /// </summary>
        /// <returns>The whole contents of the database.</returns>
        public override DatabaseContents ReadDatabase() {
            DatabaseContents contents = new DatabaseContents();
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                contents.Accounts = GetAccounts(conn);
                contents.Groups = GetGroups(conn);
                contents.Templates = GetTemplates(conn, null);
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch {
                    }
                }
            }
            return contents;
        }

        /// <summary>
        /// Imports a database without clearing existing data.
        /// </summary>
        /// <param name="contents">The contents to import.</param>
        /// <param name="logger">The logger.</param>
        public override void ImportDatabase(DatabaseContents contents, ILogProvider logger) {
            MySqlConnection conn = null;
            MultipleExceptionLogger logMultiple = new MultipleExceptionLogger();
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {
                    try {
                        AddAccounts(conn, trans, contents.Accounts, _logger);
                    } catch(Exception ex) {
                        logMultiple.MessageLog.Add(ex);
                    }
                    try {
                        AddGroups(conn, trans, contents.Groups, _logger);
                    } catch(Exception ex) {
                        logMultiple.MessageLog.Add(ex);
                    }
                    try {
                        AddTemplates(conn, trans, contents.Templates, _logger);
                    } catch(Exception ex) {
                        logMultiple.MessageLog.Add(ex);
                    }
                    trans.Commit();
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch {
                    }
                }
            }
            if(logMultiple.MessageLog.Count > 0) {
                logMultiple.MessageLog.Insert(0, new Exception(I18nUtils.GetString("Errors", "NonFatalErrors")));
                throw new MultipleException(logMultiple.MessageLog);
            }
        }

        /// <summary>
        /// Creates the database tables required in a specified file and adds the default account templates.
        /// </summary>
        public void CreateTables() {
            MySqlConnection conn = null;
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlCommand cmd = conn.CreateCommand()) {
                    cmd.CommandText = @"CREATE TABLE accounts (
                            accountid varchar(20) PRIMARY KEY NOT NULL,
                            name BLOB NOT NULL,
                            parentid varchar(20),
                            templateid varchar(20),
                            created TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
                            modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                            icon BLOB,
                            notes BLOB,
                            autotype BLOB
                        );
                        -- CREATE TRIGGER accountUpdateTime AFTER UPDATE ON accounts
                        -- BEGIN
                        --      UPDATE accounts SET modified = CURRENT_TIMESTAMP() WHERE rowid = new.rowid;
                        -- END;
                        -- CREATE TRIGGER accountInsertTime AFTER INSERT ON accounts
                        -- BEGIN
                        --      UPDATE accounts SET created = CURRENT_TIMESTAMP() WHERE rowid = new.rowid;
                        --      UPDATE accounts SET modified = CURRENT_TIMESTAMP() WHERE rowid = new.rowid;
                        -- END;
                        CREATE TABLE templates (
                            templateid varchar(20) PRIMARY KEY NOT NULL,
                            name BLOB NOT NULL,
                            shownotes int NOT NULL DEFAULT '1',
                            canuseredit INT NOT NULL DEFAULT '1'
                        );
                        CREATE TABLE templatefields (
                            templateid varchar(20) NOT NULL,
                            fieldname BLOB NOT NULL,
                            meta BLOB,
                            sortindex int NOT NULL DEFAULT '0',
                            PRIMARY KEY (templateid, fieldname(30))
                        );
                        CREATE TABLE accountfields (
                            accountid varchar(20),
                            fieldname BLOB NOT NULL,
                            fieldvalue BLOB,
                            meta BLOB,
                            sortindex int NOT NULL DEFAULT '0',
                            PRIMARY KEY (accountid, fieldname(30))
                        );
                        CREATE TRIGGER fieldLastMod AFTER UPDATE on accountfields
                        FOR EACH ROW UPDATE accounts SET modified = CURRENT_TIMESTAMP() WHERE accountid = new.accountid;
                        CREATE TABLE groups (
                            groupid varchar(20) PRIMARY KEY NOT NULL,
                            name BLOB NOT NULL,
                            parentid varchar(20), 
                            sortindex int NOT NULL DEFAULT '0'
                        );
                        CREATE TABLE meta (
                            name BLOB NOT NULL,
                            value BLOB,
                            PRIMARY KEY (name(30))
                        );
                    ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO meta (name, value) VALUES (@name, @value);";
                    //cmd.Parameters.Add("@name", DbType.Binary);
                    //cmd.Parameters.Add("@value", DbType.Binary);
                    var values = new Dictionary<string, string>() {
                        { "version", Constants.GetVersion().ToString()},
                        { "platform", "WPF"}
                    };
                    bool first = true;
                    foreach(KeyValuePair<string, string> value in values) {
                        if(first) {
                            cmd.Parameters.AddWithValue("@name", _encryptionService.EncryptString(value.Key));
                            cmd.Parameters.AddWithValue("@value", _encryptionService.EncryptString(value.Value));
                            first = false;
                        } else {
                            cmd.Parameters["@name"].Value = _encryptionService.EncryptString(value.Key);
                            cmd.Parameters["@value"].Value = _encryptionService.EncryptString(value.Value);
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
            } catch(Exception ex) {
                if(ex.Message.IndexOf("exists", StringComparison.InvariantCultureIgnoreCase) >= 0) {
                    throw new LockCryptException("Database tables already exist", 235);
                } else {
                    throw;
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch {
                    }
                }
            }
        }

        /// <summary>
        /// Changes the encryption password.
        /// </summary>
        /// <param name="newPassword">The new password.</param>
        public override void ChangePassword(string newPassword) {
            var db = ReadDatabase();
            MySqlConnection conn = null;
            MultipleExceptionLogger logMultiple = new MultipleExceptionLogger();
            try {
                conn = GetConnection();
                conn.Open();
                using(MySqlTransaction trans = conn.BeginTransaction()) {
                    List<KeyValuePair<string, string>> metaValues = new List<KeyValuePair<string, string>>();
                    using(MySqlCommand cmd = conn.CreateCommand()) {
                        cmd.Transaction = trans;
                        cmd.CommandText = @"DELETE FROM accounts;
                                DELETE FROM accountfields;
                                DELETE FROM templates;
                                DELETE FROM templatefields;
                                DELETE FROM groups;";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "SELECT name, value FROM meta";
                        MySqlDataReader reader = cmd.ExecuteReader();
                        while(reader.Read()) {
                            metaValues.Add(new KeyValuePair<string, string>(_encryptionService.DecryptString(GetBytes(0, reader)), _encryptionService.DecryptString(GetBytes(1, reader))));
                        }
                        reader.Close();
                        Encryption.EncryptionKey = newPassword;

                        var dummyArray = new byte[0];
                        cmd.CommandText = "DELETE FROM meta";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "INSERT INTO meta (name, value) VALUES (@name, @value);";
                        cmd.Parameters.AddWithValue("@name", dummyArray);
                        cmd.Parameters.AddWithValue("@value", dummyArray);
                        foreach(KeyValuePair<string, string> value in metaValues) {
                            cmd.Parameters["@name"].Value = _encryptionService.EncryptString(value.Key);
                            cmd.Parameters["@value"].Value = _encryptionService.EncryptString(value.Value);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    try {
                        AddAccounts(conn, trans, db.Accounts, _logger);
                    } catch(Exception ex) {
                        logMultiple.MessageLog.Add(ex);
                    }
                    try {
                        AddGroups(conn, trans, db.Groups, _logger);
                    } catch(Exception ex) {
                        logMultiple.MessageLog.Add(ex);
                    }
                    try {
                        AddTemplates(conn, trans, db.Templates, _logger);
                    } catch(Exception ex) {
                        logMultiple.MessageLog.Add(ex);
                    }
                    trans.Commit();
                }
            } finally {
                if(conn != null && conn.State == ConnectionState.Open) {
                    try {
                        conn.Close();
                    } catch { }
                }
            }
            if(logMultiple.MessageLog.Count > 0) {
                logMultiple.MessageLog.Insert(0, new Exception(I18nUtils.GetString("Errors", "NonFatalErrors")));
                throw new MultipleException(logMultiple.MessageLog);
            }
        }

        /// <summary>
        /// Creates, but does not open, a connection to the server.
        /// </summary>
        /// <returns>A connection to the server.</returns>
        private MySqlConnection GetConnection() {
            return new MySqlConnection(ConnectionString);
        }

        /// <summary>
        /// Gets a byte array from a column.
        /// </summary>
        /// <param name="columnIndex">Index of the column.</param>
        /// <param name="reader">The reader.</param>
        /// <returns>The contents of a Blob from the column as a byte[].</returns>
        private static byte[] GetBytes(int columnIndex, IDataRecord reader) {
            if(reader.IsDBNull(columnIndex))
                return null;
            long length = reader.GetBytes(columnIndex, 0, null, 0, 0);
            byte[] myBinaryData = new byte[length];
            reader.GetBytes(columnIndex, 0, myBinaryData, 0, myBinaryData.Length);
            return myBinaryData;
        }

        #endregion
    }
}
